package me.zogodo;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.*;

/**
 * Created by zogod on 2016/9/9.
 */
public class SqlHelper
{
    private static String url = "jdbc:mysql://localhost:3306/feng";
    private static String jar = "com.mysql.jdbc.Driver";
    private static String username = "root";
    private static String password = "123456";

    private Connection conn;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rset;
    private CachedRowSetImpl crs;

    public CachedRowSetImpl Select(String sql)
    {
        try
        {
            Class.forName(jar);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            crs = new CachedRowSetImpl();
            crs.populate(rset);
            Close();
            return crs;
        }
        catch (Exception e)
        {
            try{ Close(); }
            catch (Exception e2) { }
            return null;
        }
    }

    public CachedRowSetImpl Query(String sql)
    {
        try
        {
            Class.forName(jar);
            conn = DriverManager.getConnection(url, username, password);
            pstmt = conn.prepareStatement(sql);
            rset = pstmt.executeQuery();
            crs = new CachedRowSetImpl();
            crs.populate(rset);
            Close();
            return crs;
        }
        catch (Exception e)
        {
            try{ Close(); }
            catch (Exception e2) { }
            return null;
        }
    }

    public void insertUsePrepare() throws Exception
    {
        long start = System.currentTimeMillis();
        try (
                // 获取数据库连接
                Connection conn = DriverManager.getConnection(url
                        , user, pass);
                // 使用Connection来创建一个PreparedStatement对象
                PreparedStatement pstmt = conn.prepareStatement(
                        "insert into student_table values(null,?,1)"))

        {
            // 100次为PreparedStatement的参数设值，就可以插入100条记录
            for (int i = 0; i < 1000; i++)
            {
                pstmt.setString(1, "姓名" + i);
                pstmt.executeUpdate();
            }
            System.out.println("使用PreparedStatement费时:"
                    + (System.currentTimeMillis() - start));
        }
    }

    public int NoneQuery(String sql)
    {
        try
        {
            Class.forName(jar);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            int re = stmt.executeUpdate(sql);
            Close();
            return re;
        }
        catch (Exception e)
        {
            try{ Close(); }
            catch (Exception e2) { }
            return -1;
        }
    }

    public void Close() throws SQLException
    {
        if (conn != null) conn.close();
        if (stmt != null) stmt.close();
        if (pstmt != null) pstmt.close();
        if (rset != null) rset.close();
    }

    public CachedRowSetImpl getRowSet()
    {
        return crs;
    }

    public static void main(String[] args)
    {
        SqlHelper sh = new SqlHelper();
        if (sh.Select("SELECT id, username FROM admin") == null)
        {
            System.exit(1);
        }
        try
        {
            CachedRowSetImpl crs = sh.getRowSet();
            while (crs.next())
            {
                System.out.println("ID: " + crs.getInt(1) +
                        ",  Username: " + crs.getString(2));
            }

        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

}
